package com.oocl.daobooks.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.oocl.daobooks.dao.BookDao;
import com.oocl.daobooks.pojo.Book;
import com.oocl.daobooks.util.DBUtil;

public class BookDaoImpl implements BookDao {

	public int addBook(Book b) {
		String sql = "insert into books(id, name, pub_date, author, price, is_new, publisher) values(book_id_seq.nextval, ?, ?, ?, ?, ?, ?)";
		Connection conn = null;
		PreparedStatement pst = null;
		int count = 0;
		
		try {
			conn = DBUtil.createConnectionWithDataSource();
			pst = conn.prepareStatement(sql);
			
			pst.setString(1, b.getName());
			pst.setDate(2, new java.sql.Date(b.getPubDate().getTime()));
			pst.setString(3, b.getAuthor());
			pst.setDouble(4, b.getPrice());
			pst.setBoolean(5, b.isNew());
			pst.setString(6, b.getPublisher());
			
			count = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
		}
		return count;
	}

	public int deleteBook(Integer id) {
		String sql = "delete from books where id=?";
		Connection conn = null;
		PreparedStatement pst = null;
		int count = 0;
		
		try {
			conn = DBUtil.createConnectionWithDataSource();
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			count = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
		}
		return count;
	}

	public int updateBook(Book b) {
		String sql = "update books set name=?, pub_date=?, author=?, price=?, is_new=?, publisher=? where id=?";
		Connection conn = null;
		PreparedStatement pst = null;
		int count = 0;
		
		try {
			conn = DBUtil.createConnectionWithDataSource();
			pst = conn.prepareStatement(sql);
			
			pst.setString(1, b.getName());
			pst.setDate(2, new java.sql.Date(b.getPubDate().getTime()));
			pst.setString(3, b.getAuthor());
			pst.setDouble(4, b.getPrice());
			pst.setBoolean(5, b.isNew());
			pst.setString(6, b.getPublisher());
			pst.setInt(7, b.getId());
			
			count = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
		}
		return count;
	}

	public Book loadBook(Integer id) {
		String sql = "select * from books where id=?";
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Book b = null;
		try {
			conn = DBUtil.createConnectionWithDataSource();
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if (rs.next()) {
				b = new Book(id, rs.getString("name"), new Date(rs.getDate(
						"pub_date").getTime()), rs.getString("author"),
						rs.getDouble("price"), rs.getBoolean("is_new"),
						rs.getString("publisher"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, rs);
		}
		return b;
	}

	public List<Book> findAll() {
		List<Book> bookList = new ArrayList<>();
		String sql = "select * from books";
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.createConnectionWithDataSource();
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				Book b = new Book(rs.getInt("id"), rs.getString("name"),
						new Date(rs.getDate("pub_date").getTime()),
						rs.getString("author"), rs.getDouble("price"),
						rs.getBoolean("is_new"), rs.getString("publisher"));
				bookList.add(b);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, rs);
		}
		return bookList;
	}

	public int clear() {
		String sql = "truncate table books";
		Connection conn = null;
		PreparedStatement pst = null;
		int count = 0;
		try {
			conn = DBUtil.createConnectionWithDataSource();
			pst = conn.prepareStatement(sql);
			count = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, pst, null);
		}
		return count;
	}

}
